from elasticsearch import Elasticsearch
import datetime
import xlsxwriter
import os
es=Elasticsearch([{'host':'182.182.23.244','port':9200}],timeout=3600)
#pname="" jietong athena

def get_robot(pname):
    robot={
    "query": {
    "bool": {
    "must": [
    {
    "term": {
    "nlpRobot": pname
    }
    }
    ,
    {
    "term": {
    "agentId": "robot"
    }
    }
    ,
    {
    "term": {
    "logType": "6"
    }
    }
    ],
    "must_not": [ ],
    "should": [ ]
    }
    },
    "from": 0,
    "size": 10,
    "sort": [ ],
    "aggs": { }
    }
    return robot

def get_notrobot(pname):
    notrobot={
    "query": {
    "bool": {
    "must": [
    {
    "term": {
    "nlpRobot": pname
    }
    }
    ,
    {
    "term": {
    "logType": "6"
    }
    }
    ],
    "must_not": [
    {
    "term": {
    "agentId": "robot"
    }
    }
    ],
    "should": [ ]
    }
    },
    "from": 0,
    "size": 10,
    "sort": [ ],
    "aggs": { }
    }
    return notrobot

# 设置时间格式
def date_range(begin_date,end_date):
    dates = []
    dt = datetime.datetime.strptime(begin_date, '%Y-%m-%d')
    date = begin_date
    while date <= end_date:
        dates.append(date)
        dt = dt + datetime.timedelta(1)
        date = dt.strftime('%Y-%m-%d')
    return dates
    
def w_excel(all_list,file_path,output_file):
    #if not os.path.exists(os.path.expanduser('~')+os.sep+"docs"):
    #    os.mkdir(os.path.expanduser('~')+os.sep+"docs")
    #workbook = xlsxwriter.Workbook(os.path.expanduser('~')+os.sep+"docs"+os.sep+"result.xlsx")
    workbook = xlsxwriter.Workbook(file_path+os.sep+output_file)
    worksheet = workbook.add_worksheet("转化率")
    work_format = workbook.add_format()
    # 标题
    #worksheet.write_row('A1', first_row, work_format)
    # 日期
    for i in range(len(all_list)):
        #A的ASCII码为65
        worksheet.write_column(chr(i+65)+"1",all_list[i], work_format)
    workbook.close()
def get_list(dates):
    mylist=[["jietong-robot"],["jietong-manual"],["athena-robot"],["athena-manual"]]
    for date in dates:
        print("jietong-robot-"+date,end="\n")
        result=es.search(index="aims-nlp-log-"+date,doc_type='data',body=get_robot("jietong"))
        mylist[0].append(result['hits']['total'])
        #print(result['hits']['total'])
        print("jietong-manual-"+date,end="\n")
        result=es.search(index="aims-nlp-log-"+date,doc_type='data',body=get_notrobot("jietong"))
        #print(result['hits']['total'])
        mylist[1].append(result['hits']['total'])
        print("athena-robot-"+date,end="\n")
        result=es.search(index="aims-nlp-log-"+date,doc_type='data',body=get_robot("athena"))
        #print(result['hits']['total'])
        mylist[2].append(result['hits']['total'])
        print("athena-manual-"+date,end="\n")
        result=es.search(index="aims-nlp-log-"+date,doc_type='data',body=get_notrobot("athena"))
        #print(result['hits']['total'])
        mylist[3].append(result['hits']['total'])
    return mylist

dates=date_range("2020-09-16","2020-09-22")
file_path='D:\\wangwb\\devops\\es'
output_file='转化率.xlsx'
w_excel(get_list(dates),file_path,output_file)    

#print(mylist)
#print(date_range("2020-09-16","2020-09-22"))
#print(get_robot("jietong"))    
#print(get_notrobot("athena"))
#result=es.search(index='aims-nlp-log-2020-09-09',doc_type='data',body=body)
#print(result['hits']['total'])